package com.climb.moive.dao.impl;

import com.climb.moive.dao.MovieEvaluationDao;
import com.climb.moive.entity.MovieEvaluation;
import com.climb.moive.framework.jdbc.BeanPropertyRowMapper;
import com.climb.moive.framework.jdbc.JdbcTemplate;
import com.climb.moive.framework.jdbc.RowMapper;
import com.climb.moive.util.JdbcUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Administrator
 * 数据访问层
 */
public class MovieEvaluationDaoImpl implements MovieEvaluationDao {
    @Override
    public Integer count() throws SQLException {
        String sql="SELECT COUNT(1) from movie_evaluation";
        Connection con = null;
        Integer result=0;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            result=jdbcTemplate.queryForObject(sql,Integer.class);

        } finally {
            JdbcUtils.close(con);
        }
        return result;
    }

    @Override
    public Integer countById(Integer id) throws SQLException {
        String sql="SELECT COUNT(1) from movie_evaluation where `mem_id`=?";
        Connection con = null;
        Integer result=0;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            result=jdbcTemplate.queryForObject(sql,Integer.class,id);
        } finally {
            JdbcUtils.close(con);
        }
        return result;
    }

    @Override
    public Integer delet(Integer mid) throws SQLException {
        String sql="delete FROM movie_evaluation where `mem_id`=?";
        Connection con = null;
        Integer result=0;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            result=jdbcTemplate.update(sql,mid);
        }finally {
            JdbcUtils.close(con);
        }
        return result;
    }

    @Override
    public Integer deletByEvaluationId(Integer eid) throws SQLException {
        String sql="DELETE FROM `movie_evaluation` WHERE `id`=?";
        Connection connection=null;
        Integer result=null;
        try {
            connection=JdbcUtils.getDataSource().getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            result=  jdbcTemplate.update(sql,eid);
        }  finally {
            JdbcUtils.close(connection);
        }
        return result;
    }

    @Override
    public MovieEvaluation findEvaluationByEid(Integer eid) throws SQLException {
        String sql="SELECT * from movie_evaluation where id=?";
        Connection con = null;
        MovieEvaluation evaluation=null;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            RowMapper<MovieEvaluation> evaluationRowMapper=new BeanPropertyRowMapper<>(MovieEvaluation.class);
            evaluation=jdbcTemplate.queryForObject(sql,evaluationRowMapper,eid);
        } finally {
            JdbcUtils.close(con);
        }
        return evaluation;
    }

    @Override
    public List<MovieEvaluation> findAllEvaluation() throws SQLException {
        String sql="SELECT * from movie_evaluation";
        Connection con = null;
        List<MovieEvaluation> evaluations=null;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            RowMapper<MovieEvaluation> rowMapper=new BeanPropertyRowMapper<>(MovieEvaluation.class);
            evaluations=jdbcTemplate.query(sql,rowMapper);

        }  finally {
            JdbcUtils.close(con);
        }
        return evaluations;
    }

    @Override
    public List<MovieEvaluation> findEvaByMovieid(Integer id) throws SQLException {
        String sql="SELECT * from movie_evaluation where mov_id=?  order by publish_time desc";
        Connection con = null;
        List<MovieEvaluation> evaluations=null;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            RowMapper<MovieEvaluation> rowMapper=new BeanPropertyRowMapper<>(MovieEvaluation.class);
            evaluations=jdbcTemplate.query(sql,rowMapper,id);

        }  finally {
            JdbcUtils.close(con);
        }
        return evaluations;
    }

    @Override
    public Integer saveEvaluation(MovieEvaluation movieEvaluation) throws SQLException {
       String sql="INSERT INTO `movie_evaluation` (`mov_id`, `mem_id`, `score`, `content`, `author`, `publish_time`) VALUES (?, ?, ?, ?, ?, ?)";
       Integer result=null;
       Connection connection=null;
        try {
            connection=JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            result= jdbcTemplate.update(sql,movieEvaluation.getMov_id(),movieEvaluation.getMem_id(),movieEvaluation.getScore(),movieEvaluation.getContent(),movieEvaluation.getAuthor(),movieEvaluation.getPublish_time());
        } finally {
            JdbcUtils.close(connection);
        }
        return result;
    }

    @Override
    public List<MovieEvaluation> findEvaluaByMemberId(Integer id) throws SQLException {
        String sql="SELECT m.name,eva.score,eva.id,eva.content from movie_evaluation eva INNER JOIN movie  m on mov_id=m.id where mem_id=? order by eva.publish_time desc";
        Connection con = null;
        List<MovieEvaluation> evaluations=null;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            RowMapper<MovieEvaluation> rowMapper=new BeanPropertyRowMapper<>(MovieEvaluation.class);
            evaluations=jdbcTemplate.query(sql,rowMapper,id);

        }  finally {
            JdbcUtils.close(con);
        }
        return evaluations;
    }

}
